package com.navi.loaddata.loadService.impl.DefectData;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.loadService.impl.edcdata.SqlUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.amqp.core.AmqpTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


/**
 * @author fuchenyang
 * @date 2018/4/19
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})

public class LoadDefectData_AService {
    private static final Logger logger = LoggerFactory.getLogger(LoadDefectData_AService.class);

//    @Autowired
//    private MesRepositoryImpl mesDao;

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

//    @Autowired
//    private SqlUtils sqlUtils;

//    @Autowired
//    private AmqpTemplate rabbitTemplate;

    public void loadDefectDataMain(EtlConfD etlConfD) throws ETLException {
        Session session = gpDao.getEntityManagerSession();
        try {
            logger.info("loadDefectData Start From "+etlConfD.getRunStartTimestamp()+" To "+ etlConfD.getRunEndTimestamp());
            loadDefectData(session, etlConfD);
            logger.info("loadDefectData finish");
        } catch (ETLException e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException(LoggerUtil.stackTraceToString(e));
        }
    }

    private void loadDefectData(Session session, EtlConfD etlConfD) throws ETLException {
        //获取DefectQty字段
        StringBuffer sqlbuffSum = new StringBuffer();
        sqlbuffSum.append("select upper(column_name) column_name from information_schema.columns");
        sqlbuffSum.append(" where table_schema='sor'");
        sqlbuffSum.append(" and table_name='wpp_adefect_ope_sum'");
        sqlbuffSum.append(" and column_name like 'defectqty%'");
        sqlbuffSum.append(" order by cast(substring(column_name,11) as int4) ");
        List<String> tableColumns = new ArrayList<>();
        try {
            tableColumns = gpDao.findBySQL(sqlbuffSum.toString());  //获取表中所有列名
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
        }


        //重跑时用另一张的临时表
        String tmpTab = "";
        if(etlConfD.getJobName().toString().equals("RELOAD")){
            tmpTab = "stage.tmp_adefect_ope_sum_rld";
        }else {
            tmpTab = "stage.templates_adefect_ope_sum";
        }

        //临时表新插入数据前先删除表内的数据
        StringBuffer sqlDeltTab = new StringBuffer();
        sqlDeltTab.append("truncate table " + tmpTab);
//        sqlDeltTab.append("delete from " + tmpTab);

        try {
            SQLQuery trucTab_query = session.createSQLQuery(sqlDeltTab.toString());
            trucTab_query.executeUpdate();
        }catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlDeltTab.toString():  **********    "+sqlDeltTab.toString());
            e.printStackTrace();
            throw new ETLException("ERROR hisAndExtToTmp Truncate "+ tmpTab +":" + LoggerUtil.stackTraceToString(e));

        }


        //插入新查询出来的defect明细数据
        StringBuffer sqlInsrDef = new StringBuffer();
        sqlInsrDef.append("insert into "+ tmpTab +" (evt_timestamp, cald_date, shf_key, shf_id, hour_key, ");
        sqlInsrDef.append("shop_id, lot_id, glass_id, panel_id, ope_id, eqpt_id, prod_id, owner_id, grade_id, ");
        sqlInsrDef.append("defect_size, real_flg, defect_code) ");
        sqlInsrDef.append("select evt_timestamp, cald_date, shf_key_fk as shf_key, shf_id, hour_key_fk as hour_key, shop_id, ");
        sqlInsrDef.append("lot_id, glass_id, panel_id, ope_id, eqpt_id, prod_id, owner as owner_id, pnl_grade as grade_id, ");
        sqlInsrDef.append("defect_size, real_flg, defect_code ");
        sqlInsrDef.append("from sor.wpp_adefect_f ");

        //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
        if(etlConfD.getExt1().toString().equals("Y")) {
            sqlInsrDef.append("where evt_timestamp >= '" + etlConfD.getRunStartTimestamp() + "'");
            sqlInsrDef.append(" and evt_timestamp < '" + etlConfD.getRunEndTimestamp() + "'");
        }
        else {
            SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = new Date();
            Date endDate = new Date();
            try{
                startDate = sqldate.parse(etlConfD.getRunStartTimestamp().toString());
                endDate = sqldate.parse(etlConfD.getRunEndTimestamp().toString());
            }
            catch (ParseException e) {
                e.printStackTrace();
            }
            Calendar calStart = Calendar.getInstance();
            calStart.setTime(startDate);
            calStart.add(Calendar.MONTH, -1);

            Calendar calEnd = Calendar.getInstance();
            calEnd.setTime(endDate);
            calEnd.add(Calendar.DATE, 10);

            sqlInsrDef.append(" where evt_timestamp >= '" + sqldate.format(calStart.getTime()) + "'");
            sqlInsrDef.append(" and evt_timestamp < '" + sqldate.format(calEnd.getTime()) + "'");
            sqlInsrDef.append(" and db_timestamp >= '" + etlConfD.getRunStartTimestamp() + "'");
            sqlInsrDef.append(" and db_timestamp < '" + etlConfD.getRunEndTimestamp() + "'");
        }

        sqlInsrDef.append(" and real_flg = 'Y'");
        sqlInsrDef.append(" and defect_code not like '*%'");

        try {
            SQLQuery InsrDef_query = session.createSQLQuery(sqlInsrDef.toString());
            InsrDef_query.executeUpdate();
        }catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlInsrDef.toString():  **********    "+sqlInsrDef.toString());
            e.printStackTrace();
            throw new ETLException("ERROR hisAndExtToTmp Insert Temp Table "+ tmpTab +":" + LoggerUtil.stackTraceToString(e));

        }


        //By ope_id、panel_id汇总wpp_adefect_f数据
        StringBuffer sqlbuffColumn = new StringBuffer();
        sqlbuffColumn.append("select evt_timestamp,cald_date,shf_key,shf_id,hour_key,shop_id,lot_id,glass_id,panel_id,ope_id,eqpt_id,");
        sqlbuffColumn.append("prod_id,owner_id,grade_id,defect_size");

        tableColumns.forEach(column -> {
            sqlbuffColumn.append(", coalesce("+ column + ", 0)");
        });

        sqlbuffColumn.append(" from (");
        sqlbuffColumn.append("select evt_timestamp, cald_date, shf_key, shf_id, hour_key, ");
        sqlbuffColumn.append("shop_id, lot_id, glass_id, panel_id, ope_id, eqpt_id, prod_id, ");
        sqlbuffColumn.append("owner_id, grade_id, defect_size, ");
        sqlbuffColumn.append("row_number() over(partition by evt_timestamp,ope_id,panel_id,defect_size order by evt_timestamp desc) rownum");

        tableColumns.forEach(column -> {
            sqlbuffColumn.append(", max("+ column + ") "+ column);
        });

        sqlbuffColumn.append(" from (");
        sqlbuffColumn.append("select a.evt_timestamp,");
        sqlbuffColumn.append("a.cald_date,");
        sqlbuffColumn.append("a.shf_key,");
        sqlbuffColumn.append("a.shf_id,");
        sqlbuffColumn.append("a.hour_key,");
        sqlbuffColumn.append("a.shop_id,");
        sqlbuffColumn.append("a.lot_id,");
        sqlbuffColumn.append("a.glass_id,");
        sqlbuffColumn.append("a.panel_id,");
        sqlbuffColumn.append("a.ope_id,");
        sqlbuffColumn.append("a.eqpt_id,");
        sqlbuffColumn.append("a.prod_id,");
        sqlbuffColumn.append("a.owner_id,");
        sqlbuffColumn.append("a.grade_id,");
        sqlbuffColumn.append("case when a.defect_size is null then '***' ");
        sqlbuffColumn.append("else a.defect_size ");
        sqlbuffColumn.append("end defect_size ");

        tableColumns.forEach(column -> {
            sqlbuffColumn.append(",case when (b.defect_code_seq = '"+ column + "')");
            sqlbuffColumn.append(" then count(b.defect_code_seq)");
            sqlbuffColumn.append(" else null end "+ column);
        });

        sqlbuffColumn.append(" from stage.templates_adefect_ope_sum a,dm.m_defect_gp_rln_d b");
        sqlbuffColumn.append(" where a.ope_id = b.ope_id");
        sqlbuffColumn.append(" and a.shop_id = b.shop_id");
        sqlbuffColumn.append(" and a.defect_code = b.defect_code");
        sqlbuffColumn.append(" and b.dl_flg ='D'");
        sqlbuffColumn.append(" group by");
        sqlbuffColumn.append(" a.evt_timestamp,");
        sqlbuffColumn.append(" a.cald_date,");
        sqlbuffColumn.append(" a.shf_key,");
        sqlbuffColumn.append(" a.shf_id,");
        sqlbuffColumn.append(" a.hour_key,");
        sqlbuffColumn.append(" a.shop_id,");
        sqlbuffColumn.append(" a.lot_id,");
        sqlbuffColumn.append(" a.glass_id,");
        sqlbuffColumn.append(" a.panel_id,");
        sqlbuffColumn.append(" a.ope_id,");
        sqlbuffColumn.append(" a.eqpt_id,");
        sqlbuffColumn.append(" a.prod_id,");
        sqlbuffColumn.append(" a.owner_id,");
        sqlbuffColumn.append(" a.grade_id,");
        sqlbuffColumn.append(" a.defect_size,");
        sqlbuffColumn.append(" b.defect_code_seq");
        sqlbuffColumn.append(") c ");
        sqlbuffColumn.append("group by evt_timestamp, cald_date, shf_key, shf_id, hour_key, ");
        sqlbuffColumn.append("shop_id, lot_id, glass_id, panel_id, ope_id, eqpt_id, prod_id, ");
        sqlbuffColumn.append("owner_id, grade_id, defect_size) d ");
        sqlbuffColumn.append("where rownum = 1 ");
//        sqlbuffColumn.append("order by evt_timestamp, ope_id, panel_id, defect_size");


        //By ope_id、panel_id删除重复数据
        StringBuffer sqlbuffDel = new StringBuffer();
        sqlbuffDel.append("delete from wpp_adefect_ope_sum a ");
        sqlbuffDel.append(" where exists (");
        sqlbuffDel.append(" select 1 ");
        sqlbuffDel.append(" from ("+ sqlbuffColumn +") b");
        sqlbuffDel.append(" where a.ope_id = b.ope_id");
        sqlbuffDel.append(" and a.panel_id = b.panel_id)");
        sqlbuffDel.append(" and a.evt_timestamp < '" + etlConfD.getRunEndTimestamp() + "'");

        try {
            SQLQuery del_query = session.createSQLQuery(sqlbuffDel.toString());
            del_query.executeUpdate();
        }catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlbuffDel.toString():  **********    "+sqlbuffDel.toString());
            e.printStackTrace();
            throw new ETLException("ERROR hisAndExtToTmp DELETE:" + LoggerUtil.stackTraceToString(e));

        }


        //重跑数据，删除Summary表的对应数据
        if(etlConfD.getExt1().toString().equals("Y")) {
            StringBuffer sqlDelTab = new StringBuffer();
            sqlDelTab.append("delete from wpp_adefect_ope_sum");
            sqlDelTab.append(" where evt_timestamp >= '" + etlConfD.getRunStartTimestamp() + "'");
            sqlDelTab.append(" and evt_timestamp < '" + etlConfD.getRunEndTimestamp() + "'");

            try {
                SQLQuery trucTab_query = session.createSQLQuery(sqlDelTab.toString());
                trucTab_query.executeUpdate();
            } catch (Exception e) {
                logger.error(LoggerUtil.stackTraceToString(e));
                logger.error("************   sqlDelTab.toString():  **********    " + sqlDelTab.toString());
                e.printStackTrace();
                throw new ETLException("ERROR hisAndExtToTmp Delete wpp_adefect_ope_sum from " + etlConfD.getRunStartTimestamp() + " to " + etlConfD.getRunEndTimestamp() + ":" + LoggerUtil.stackTraceToString(e));

            }
        }

        //插入汇总数据
        StringBuffer sqlbuffInsr = new StringBuffer();
        sqlbuffInsr.append("insert into wpp_adefect_ope_sum ");
        sqlbuffInsr.append("(evt_timestamp,cald_date,shf_key,shf_id,hour_key,shop_id,lot_id,glass_id,panel_id,ope_id,eqpt_id,");
        sqlbuffInsr.append("prod_id,owner_id,grade_id,defect_size");

        tableColumns.forEach(column -> {
            sqlbuffInsr.append(", "+ column);
        });

        sqlbuffInsr.append(") " +  sqlbuffColumn);
        sqlbuffInsr.append("order by evt_timestamp, ope_id, panel_id, defect_size");


        try {
            SQLQuery insr_query = session.createSQLQuery(sqlbuffInsr.toString());
            insr_query.executeUpdate();
        }catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlbuffInsr.toString():  **********    "+sqlbuffInsr.toString());
            e.printStackTrace();
            throw new ETLException("ERROR hisAndExtToTmp INSERT:" + LoggerUtil.stackTraceToString(e));

        }

        //删除临时表内的数据
        StringBuffer sqltrucTab = new StringBuffer();
        sqltrucTab.append("truncate table " + tmpTab);
//        sqltrucTab.append("delete from " + tmpTab);

        try {
            SQLQuery trucTab_query = session.createSQLQuery(sqltrucTab.toString());
            trucTab_query.executeUpdate();
        }catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqltrucTab.toString():  **********    "+sqltrucTab.toString());
            e.printStackTrace();
            throw new ETLException("ERROR hisAndExtToTmp Truncate "+ tmpTab +":" + LoggerUtil.stackTraceToString(e));

        }

    }

}
